/*********************************************************************************
 *      Copyright:  (C) 2021 ljl
 *                  All rights reserved.
 *
 *       Filename:  sqlite_db.c
 *    Description:  This file 
 *                 
 *        Version:  1.0.0(2021年12月14日)
 *         Author:  Li Jiale <244871365@qq.com>
 *      ChangeLog:  1, Release initial version on "2021年12月14日 20时40分47秒"
 *                 
 ********************************************************************************/
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <unistd.h>
#include "sqlite3.h"
#include "sqlite_db.h"

static sqlite3          *db = NULL;

#define TABLE_NAME      "temperature"

int init_db(const char *db_file)
{
    int                      rv = 0;
    char                     sql[128];
    char                    *errmsg = NULL;

    if( !db_file )
    {
        //log_err("ERROR: Invalid input arguments\n");
        printf("ERROR: Invalid input arguments\n");
        return -1;
    }

    if( SQLITE_OK != sqlite3_open(db_file, &db) )
    {
        //log_err("open/create sqlite database '%s', failure\n", db_file);
        printf("open/create sqlite database '%s', failure\n", db_file);
        return -2;
    }

    memset(sql, 0, sizeof(sql));
    snprintf(sql, sizeof(sql), "CREATE TABLE IF NOT EXISTS %s(packet BLOB);",TABLE_NAME);
    if( SQLITE_OK != sqlite3_exec(db, sql, NULL, NULL, &errmsg) )
    {
        //log_err("create temperature table in database file '%s' failure: %s\n", db_file, errmsg);
        printf("create temperature table in database file '%s' failure: %s\n", db_file, errmsg);
        sqlite3_free(errmsg);
        rv = -3;
        goto out; 
    }

out:
    if( rv < 0 )
    {
        //log_err("initialize database file '%s' failure, rv=%d\n", db_file, rv);
        printf("initialize database file '%s' failure, rv=%d\n", db_file, rv);
        sqlite3_close(db);
        unlink(db_file);
    }
    else
    {
        //log_nrml("initialize database file '%s' ok\n", db_file);
        printf("initialize database file '%s' ok\n", db_file);
    }

    return rv;
}

int count_db(void)
{
    int                      rv = 0; 
    char                     sql[128];
    char                    *errmsg = NULL;
    char                   **result;
    int                      rownum;
    int                      colnum;

    if( !db )
    {
        //log_err("ERROR: SQLite database not initialise.\n");
        printf("ERROR: SQLite database not initialise.\n");
        return -1;
    }

    memset(sql, 0, sizeof(sql));
    snprintf(sql, sizeof(sql), "select count(*) from %s;", TABLE_NAME);

    if( SQLITE_OK != sqlite3_get_table(db, sql, &result, &rownum, &colnum, &errmsg) )
    {
        //log_err("query records count from database failure: %s\n", errmsg);
        printf("query records count from database failure: %s\n", errmsg);
        sqlite3_free(errmsg);
        return -1;
    }

    if(  rownum <= 0 )
    {
        //log_err("query records count from database failure: %s\n", errmsg);
        printf("count failure\n");
        sqlite3_free(errmsg);
        return -3;
    }

    rv = atoi(result[1]);
    return rv;
    //return rownum;
}

int push_db(temp_data_t *data, int bytes)
{
    int                      rv = 0;
    char                     sql[128];
    sqlite3_stmt            *stat = NULL;

    if( !data )
    {
        //log_err("ERROR: invalid input arguments\n");
        printf("ERROR: invalid input arguments\n");
        return -1;
    }

    if( !db )
    {
        //log_err("ERROR: SQLite database not initialise.\n");
        printf("ERROR: SQLite database not initialise.\n");
        return -2;
    }

    memset(sql, 0, sizeof(sql));
    snprintf(sql, sizeof(sql), "insert into %s(packet)values(?)", TABLE_NAME);

    rv = sqlite3_prepare_v2(db, sql, -1, &stat, NULL);
    if( SQLITE_OK!=rv || !stat )
    {
        //log_err("database operate sqlite3_prepare_v2() failure\n");
        printf("database operate sqlite3_prepare_v2() failure\n");
        rv = -3;
        goto out;
    }

    if( SQLITE_OK != sqlite3_bind_blob(stat, 1, data, bytes, NULL) )
    {
        //log_err("database operate  sqlite3_bind_blob() failure\n");
        printf("database operate  sqlite3_bind_blob() failure\n");
        rv = -4;
        goto out;
    }

    rv = sqlite3_step(stat);
    {
         if( SQLITE_DONE!=rv && SQLITE_ROW!=rv )
         {
             //log_err("database operate sqlite3_step() failure\n");
            printf("database operate sqlite3_step() failure\n");
            rv = -5;
            goto out;
         }
    }

out:
    sqlite3_finalize(stat);
     
    if(rv < 0)
    {
        //log_err("ERROR: push temperature record into sqlite database failure\n");
        printf("ERROR: push temperature record into sqlite database failure\n");
        return  rv;
    }
     
    //log_nrml("push temperature record into sqlite database okay\n");
    printf("push temperature record into sqlite database okay\n");
    return 0;
}

int pop_db(temp_data_t *data, int bytes)
{
    int                      rv = 0;
    char                     sql[128];
    sqlite3_stmt            *stat = NULL;
    const temp_data_t       *blob_ptr;

    if( !data )
    {
        //log_err("ERROR: invalid input arguments\n");
        printf("ERROR: invalid input arguments\n");
        return -1;
    }
     
    if( !db )
    {
        //log_err("ERROR: SQLite database not initialise.\n");
        printf("ERROR: SQLite database not initialise.\n");
        return -2;
    }
     
    memset(sql, 0, sizeof(sql));
    snprintf(sql, sizeof(sql), "select packet from %s limit 1;", TABLE_NAME);
    rv = sqlite3_prepare_v2(db, sql, -1, &stat, NULL);
    if(SQLITE_OK!=rv || !stat)
    {
        //log_err("database operate sqlite3_prepare_v2() failure\n");
        printf("database operate sqlite3_prepare_v2() failure\n");
        rv = -3;
        goto out;
    }
     
    rv = sqlite3_step(stat);
    if( SQLITE_DONE!=rv && SQLITE_ROW!=rv )
    {
        //log_err("database operate sqlite3_step() failure\n");
        printf("database operate sqlite3_step() failure\n");
        rv = -5;
        goto out;
    }
    blob_ptr =(temp_data_t *)sqlite3_column_blob(stat, 0);
    memset(data, 0, bytes);
   
    printf("%s\n",blob_ptr);
    printf("220\n");
    memcpy(data, blob_ptr, bytes);
    printf("222\n");
     
out:
    sqlite3_finalize(stat);
     
    if(rv < 0)
    {
        //log_err("ERROR: push temperature record into sqlite database failure\n");
        printf("ERROR: push temperature record into sqlite database failure\n");
        return  rv;
    }
     
    //log_nrml("push temperature record into sqlite database okay\n");
    printf("pop temperature record into sqlite database okay\n");
    return 0;
}

int del_first_db()
{
    char          sql[128];
    char          *errmsg = NULL;

    memset(sql, 0, sizeof(sql));
    snprintf(sql, sizeof(sql), "delete from %s limit 0,1;", TABLE_NAME);
    
    if( !db )
    {
        //log_err("ERROR: SQLite database not initialise.\n");
        printf("ERROR: SQLite database not initialise.\n");
        return -1;
    }

    if( SQLITE_OK != sqlite3_exec(db, sql, NULL, NULL, &errmsg) )
    {
        //log_err("ERROR: delete first temperature record from sqlite database failure\n");
        printf("ERROR: delete first temperature record from sqlite database failure\n");
        sqlite3_free(errmsg);
        return -2;
    }

    //log_nrml(" temperature first record database delete.\n");
    printf(" temperature first record database delete.\n");

    return 0;
}

void term_db(void)
{
    if( db )
    sqlite3_close(db); 
        
    //log_nrml("terminate client sqlite database\n");
    printf("terminate client sqlite database\n");
}

